package com.cn.wanxi.dao.impl;

import com.cn.wanxi.dao.UserDao;
import com.cn.wanxi.model.UserModel;
import com.cn.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据实现层
 * 1.封装sql语句
 * 2.得到结果集 （数据库查询或者增加修改删除的结果）
 * 3.封装成Java所需要的对象
 * 4.返回给service层
 */
public class UserImpl implements UserDao {
    @Override
    public UserModel findByUsernameAndPassword(UserModel userModel) {
//        1.封装sql语句
        String sql = "select * from user where " +
                "username='" + userModel.getUsername() + "' " +
                "and password ='" + userModel.getPassword() + "'";
//        2.得到结果集,连接数据库，从数据库操作数据
        ResultSet query = JDBC.query(sql);
//        3.解析结果集
        UserModel model = null;
        try {
            while (query.next()) {
                model = new UserModel();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return model;
    }

    @Override
    public int add(UserModel userModel) {
        String sql = "insert into user (email,username,password)" +
                "values('" + userModel.getEmail() + "','" + userModel.getUsername() + "'," +
                "'" + userModel.getPassword() + "')";
        return JDBC.update(sql);
    }

    @Override
    public int update(UserModel userModel) {
//        String sql = "update user set username='" + userModel.getUsername() + "'" +
//                ",sex='" + userModel.getSex() + "',phone='" + userModel.getPhone() + "'" +
//                " where id=" + userModel.getId();
        String sql = "UPDATE \n" +
                "`user` SET " +
                "`address` = '" + userModel.getAddress() + "', " +
                " `email` = '" + userModel.getEmail() + "'," +
                " `enable` = '" + userModel.getEnable() + "', " +
                " `phone` = 'a'," +
                " `sex` = 'a', " +
                "`username` = 'a'," +
                " `hobby` = 'a', " +
                "`birthday` = 'a' " +
                "WHERE (`id` = '" + userModel.getId() + " ');\n";
//        String sql = "update user set username=? ,phone=? where id=?";
//        List list = new ArrayList<>();
//        list.add(userModel.getUsername());
//        list.add(userModel.getPhone());
//        list.add(userModel.getId());
        return JDBC.update(sql);
    }

    @Override
    public int delete(UserModel userModel) {
        String sql = "delete from user where id=" + userModel.getId();
        return JDBC.update(sql);
    }

    @Override
    public List<UserModel> findAll(UserModel userModel) {
//        limit  (curPage-1)*pageSize,pageSize
        String sql = "select * from user where true ";
        sql += getSql(userModel);
//        int start=(userModel.getCurPage()-1)*userModel.getPageSize();
        sql += " limit  " + (userModel.getCurPage() - 1) * +userModel.getPageSize() + "," + userModel.getPageSize() + "";
        return getList(sql);
    }

    private List<UserModel> getList(String sql) {
        List<UserModel> list = new ArrayList<>();
        ResultSet query = JDBC.query(sql);
        try {
            while (query.next()) {
                UserModel model = new UserModel();
                model.setId(query.getInt("id"));
                model.setUsername(query.getString("username"));
                model.setEmail(query.getString("email"));
                model.setPhone(query.getString("phone"));
                model.setHobby(query.getString("hobby"));
                model.setSex(query.getString("sex"));
                model.setEnable(query.getInt("enable"));
                model.setBirthday(query.getString("birthday"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public UserModel findById(UserModel userModel) {
        String sql = "select * from user where id=" + userModel.getId();
        List<UserModel> list = getList(sql);
        if (list.size() == 0) {
            list.add(null);
        }
        return list.get(0);
    }

    @Override
    public int count(UserModel userModel) {
        String sql = "select count(*)  count from user where 1=1";
        sql += getSql(userModel);
        ResultSet query = JDBC.query(sql);
        try {
            while (query.next()) {
                return query.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 拼接sql语句
     *
     * @param userModel
     * @return
     */
    private String getSql(UserModel userModel) {
        String sql = "";
        if (!"".equals(userModel.getUsername()) ) {
            sql += " and username like '%" + userModel.getUsername() + "%'";
        }
        if (!"".equals(userModel.getSex())) {
            sql += " and sex = '" + userModel.getSex() + "'";
        }

        if ("".equals(userModel.getStart()) && !"".equals(userModel.getEnd())) {
            sql += " and birthday <='" + userModel.getEnd() + "'";
        }
        if (!"".equals(userModel.getStart()) && "".equals(userModel.getEnd())) {
            sql += " and birthday >='" + userModel.getStart() + "'";
        }
        if (!"".equals(userModel.getStart()) && !"".equals(userModel.getEnd())) {
//            sql += " and birthday <'" + userModel.getEnd() + "' and birthday >'"+userModel.getStart()+"'";
            sql += " and birthday between '" + userModel.getStart() + "' and '" + userModel.getEnd() + "'";
        }
        if (userModel.getEnable() != -1) {
            sql += " and enable=" + userModel.getEnable();
        }
        if (!"".equals(userModel.getHobby())) {
//            find_in_set
//            String[] hobbys = userModel.getHobby().split(",");
//            for (int i = 0; i < hobbys.length; i++) {
//                sql += " and find_in_set('" + hobbys[i] + "',hobby)";
//            }
            String[] hobbys = userModel.getHobby().split(",");
            sql += " and (";
            for (int i = 0; i < hobbys.length; i++) {
                if (i == 0) {
                    sql += "  find_in_set('" + hobbys[i] + "',hobby)";
                } else {
                    sql += " or find_in_set('" + hobbys[i] + "',hobby)";
                }
            }
            sql += ")";
        }
        return sql;
    }
}
